package org.jsets.fastboot.database;

import java.io.StringReader;
import java.util.Arrays;
import com.google.common.collect.Lists;
import net.sf.jsqlparser.expression.Function;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectExpressionItem;
import net.sf.jsqlparser.statement.update.Update;

/**
 * 
 * SQL工具
 * @author wj596
 *
 */
public class SqlUtils {

	private static final CCJSqlParserManager parserManager = new CCJSqlParserManager();

	/**
	 * 获取语句类型
	 */
	public static StatementType getStatementType(String sql) {
		StringReader stringReader = new StringReader(sql);
		try {
			Statement statement = parserManager.parse(stringReader);
			if (statement instanceof Insert) {
				return StatementType.INSERT;
			}
			if (statement instanceof Delete) {
				return StatementType.DELETE;
			}
			if (statement instanceof Update) {
				return StatementType.UPDATE;
			}
			if (statement instanceof Select) {
				return StatementType.SELECT;
			}
			throw new RuntimeException("错误的SQL.");
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			stringReader.close();
		}
	}
	
	/**
	 * 基于sql生成select Count 语句
	 */
	public static String remouldCountSql(String sql) {
		StringReader stringReader = new StringReader(sql);
		try {
			Statement statement = parserManager.parse(stringReader);
			if (!(statement instanceof Select)) {
				throw new RuntimeException("只支持Slect语句.");
			}
			Select select = (Select)statement;
			PlainSelect plainSelect = (PlainSelect)select.getSelectBody();
			Function function = new Function();
            function.setName("count");
            ExpressionList expressionList = new ExpressionList();
            expressionList.setExpressions(Arrays.asList(new Column("1")));
            function.setParameters(expressionList);
			SelectExpressionItem count = new SelectExpressionItem();
			count.setExpression(function);
			plainSelect.setSelectItems(Lists.newArrayList(count));
			return plainSelect.toString();
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			stringReader.close();
		}
	}
	
	/**
	 * 基于sql生成分页查询 语句
	 */
    public static String remouldPaginationSql(DatabaseType dbType, String originalSql, long currentPage, long limit) {
    	long offset = 0;
    	if (currentPage > 0) {
    		offset =  (currentPage - 1) * limit;
        }

    	switch(dbType){
        case MySQL:
            StringBuilder sb = new StringBuilder(originalSql).append(" LIMIT ");
            if (offset != 0L) {
            	sb.append(offset).append(", ");
            } 
            sb.append(limit);
        	return sb.toString();
        case Oracle:
            long end = (offset >= 1) ? (offset + limit) : limit;
            String sql = "SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( " +
                originalSql + " ) TMP WHERE ROWNUM <=" + end + ") WHERE ROW_ID > " + offset;
        	return sql;
        case Oracle12c:
            return originalSql + " OFFSET " + offset + " ROWS FETCH NEXT " + limit + " ROWS ONLY";
        default:
            throw new RuntimeException("不支持的数据库类");
    	}
    }

	public static void main(String[] args) {
//		String sql = "select * from sys_user u where id in (select id from t_ab) and name = 'bb'";
//		System.out.println(remouldCountSql(sql));
		
		String sql = "select * from sys_user u where id in (select id from t_ab) ";
		System.out.println(remouldCountSql(sql));
	
	}

}